package com.ruoyi.bearing.service.impl;

import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.*;

import com.fasterxml.jackson.core.JsonParser;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.ruoyi.bearing.domain.Componentdata;
import com.ruoyi.bearing.mapper.ComponentdataMapper;
import com.ruoyi.common.config.RuoYiConfig;
import com.ruoyi.common.utils.DateUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.stereotype.Service;
import com.ruoyi.bearing.mapper.BearingPretreatmentMapper;
import com.ruoyi.bearing.domain.BearingPretreatment;
import com.ruoyi.bearing.service.IBearingPretreatmentService;
import org.springframework.transaction.annotation.Transactional;

/**
 * 数据预处理Service业务层处理
 *
 * @author xxx
 * @date 2025-04-07
 */
@Service
public class BearingPretreatmentServiceImpl implements IBearingPretreatmentService
{
    @Autowired
    private BearingPretreatmentMapper bearingPretreatmentMapper;
    @Autowired
    private ComponentdataMapper componentdataMapper;

    @Autowired
    private RuoYiConfig ruoYiConfig;

    @Autowired
    private StringRedisTemplate stringRedisTemplate;

    /**
     * 查询数据预处理
     *
     * @param id 数据预处理主键
     * @return 数据预处理
     */
    @Override
    public BearingPretreatment selectBearingPretreatmentById(Long id)
    {
        return bearingPretreatmentMapper.selectBearingPretreatmentById(id);
    }

    /**
     * 查询数据预处理列表
     *
     * @param bearingPretreatment 数据预处理
     * @return 数据预处理
     */
    @Override
    public List<BearingPretreatment> selectBearingPretreatmentList(BearingPretreatment bearingPretreatment)
    {
        return bearingPretreatmentMapper.selectBearingPretreatmentList(bearingPretreatment);
    }

    /**
     * 新增数据预处理
     *
     * @param bearingPretreatment 数据预处理
     * @return 结果
     */
    @Override
    @Transactional
    public int insertBearingPretreatment(BearingPretreatment bearingPretreatment)
    {
        String uploadDir = ruoYiConfig.getUpload();
        String pretreatmentFile = uploadDir + "/" + bearingPretreatment.getPretreatmentFile();
        bearingPretreatment.setPretreatmentFile(pretreatmentFile);

        // 查询数据集的文件
        Long componentdataId = bearingPretreatment.getComponentdataId();
        Componentdata componentdata = componentdataMapper.selectComponentdataById(componentdataId);
        String dataFilePath =  componentdata.getDataFilePath();


        // 读取.xlsx文件数据。进行计算后，生成新的文件。同时更新数据集文件路径。

        // 预处理数据集，目前只有typeName = MOSFET 类型
        if ("MOSFET".equals(bearingPretreatment.getTypeName())) {
            // 读取数据集的文件
            pretreatment(dataFilePath,pretreatmentFile);
        }
        componentdata.setCharacteristicFilePath(pretreatmentFile);
        componentdataMapper.updateComponentdata(componentdata);
        bearingPretreatment.setCreateTime(DateUtils.getNowDate());
        return bearingPretreatmentMapper.insertBearingPretreatment(bearingPretreatment);
    }

    /**
     * 修改数据预处理
     *
     * @param bearingPretreatment 数据预处理
     * @return 结果
     */
    @Override
    public int updateBearingPretreatment(BearingPretreatment bearingPretreatment)
    {
        return bearingPretreatmentMapper.updateBearingPretreatment(bearingPretreatment);
    }

    /**
     * 批量删除数据预处理
     *
     * @param ids 需要删除的数据预处理主键
     * @return 结果
     */
    @Override
    public int deleteBearingPretreatmentByIds(Long[] ids)
    {
        return bearingPretreatmentMapper.deleteBearingPretreatmentByIds(ids);
    }

    /**
     * 删除数据预处理信息
     *
     * @param id 数据预处理主键
     * @return 结果
     */
    @Override
    public int deleteBearingPretreatmentById(Long id)
    {
        return bearingPretreatmentMapper.deleteBearingPretreatmentById(id);
    }

    // 分页查询
    @Override
    public Map<String, Object> getPretreatmentData(int page, int size) {
        Set<String> keys = stringRedisTemplate.keys("pretreatmentData");
        int total = 0;
        List<Map<String, Object>> result = new ArrayList<>();
        int start = (page - 1) * size;
        int end = start + size;
        int count = 0;

        ObjectMapper objectMapper = new ObjectMapper();
        List<Map<String, Object>> jsonData = null;

        for (String key : keys) {
            try {
                String json = stringRedisTemplate.opsForValue().get(key);
                jsonData = objectMapper.readValue(json, objectMapper.getTypeFactory().constructCollectionType(List.class, Map.class));
                jsonData.remove(0);
                total = jsonData.size();
                for (Map<String, Object> item : jsonData) {
                    if (count >= end) break;
                    if (count >= start) {
                        result.add(item);
                    }
                    count++;
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        Map<String, Object> response = new HashMap<>();
        response.put("total", total);
        response.put("data", result);
        return response;
    }

    @Override
    public Map<String, Object> getAllPretreatmentData() {
        Set<String> keys = stringRedisTemplate.keys("pretreatmentData");
        int total = 0;
        ObjectMapper objectMapper = new ObjectMapper();
        List<Map<String, Object>> jsonData = null;
        for (String key : keys) {
            try {
                String json = stringRedisTemplate.opsForValue().get(key);
                jsonData = objectMapper.readValue(json, objectMapper.getTypeFactory().constructCollectionType(List.class, Map.class));
                jsonData.remove(0);
                total = jsonData.size();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        Map<String, Object> response = new HashMap<>();
        response.put("total", total);
        response.put("data", jsonData);
        return response;
    }

    public void pretreatment(String dataFilePath,String pretreatmentFile) {
        try {
            // 读取原始 Excel 文件
            FileInputStream fileInputStream = new FileInputStream(dataFilePath);
            Workbook workbook = new XSSFWorkbook(fileInputStream);
            Sheet sheet = workbook.getSheetAt(0);

            // 获取倒数第 2 列和倒数第 3 列的索引
            int lastColumnIndex = sheet.getRow(0).getLastCellNum() - 1; // 倒数第 1 列的索引
            int columnGIndex = lastColumnIndex - 1; // 倒数第 2 列的索引
            int columnFIndex = lastColumnIndex - 2; // 倒数第 3 列的索引

            // 存储计算结果
            List<BigDecimal> results = new ArrayList<>();

            // 创建 DataFormatter 实例
            DataFormatter dataFormatter = new DataFormatter();
            // 遍历每一行，进行计算
            List<HashMap<String, Object>> jsonData = new ArrayList<>();
            // 遍历每一行，进行计算
            for (Row row : sheet) {
                if (row.getRowNum() == 0){
                    // 处理标题行
                    HashMap<String, Object> headerMap = new HashMap<>();
                    for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                        Cell cell = row.getCell(colIndex);
                        headerMap.put(dataFormatter.formatCellValue(cell), colIndex);
                    }
                    jsonData.add(headerMap);
                }else{
                    // 处理数据行
                    HashMap<String, Object> rowData = new HashMap<>();
                    for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                        Cell cell = row.getCell(colIndex);
                        rowData.put(dataFormatter.formatCellValue(sheet.getRow(0).getCell(colIndex)), dataFormatter.formatCellValue(cell));
                    }
                    Cell cellF = row.getCell(columnFIndex);
                    Cell cellG = row.getCell(columnGIndex);

                    // 获取单元格值并转换为 BigDecimal
                    BigDecimal valueF = new BigDecimal(dataFormatter.formatCellValue(cellF));
                    BigDecimal valueG = new BigDecimal(dataFormatter.formatCellValue(cellG));

                    // 计算倒数第 3 列除以倒数第 2 列
                    BigDecimal result = valueF.divide(valueG, 10, BigDecimal.ROUND_HALF_UP); // 保留 10 位小数
                    rowData.put("R_DS(on)", result.toString());
                    results.add(result);
                    jsonData.add(rowData);
                }
            }

            // 创建新的 Excel 文件
            Workbook newWorkbook = new XSSFWorkbook();
            Sheet newSheet = newWorkbook.createSheet("Processed Data");

            // 复制原始数据到新文件
            for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row newRow = newSheet.createRow(rowIndex);

                Row oldRow = sheet.getRow(rowIndex);
                if (oldRow != null) {
                    for (int colIndex = 0; colIndex < oldRow.getLastCellNum(); colIndex++) {
                        Cell oldCell = oldRow.getCell(colIndex);
                        Cell newCell = newRow.createCell(colIndex);
                        newCell.setCellValue(dataFormatter.formatCellValue(oldCell));
                    }
                }
            }

            // 添加计算结果到新列
            Row headerRow = newSheet.getRow(0);
            headerRow.createCell(lastColumnIndex + 1).setCellValue("R_DS(on)");
            // 设置标题行的字体为加粗
            Font font = newWorkbook.createFont();
            font.setBold(true); // 设置加粗

            CellStyle style = newWorkbook.createCellStyle();
            style.setFont(font);

            // 应用加粗样式到标题行的所有单元格
            for (int i = 0; i <= lastColumnIndex + 1; i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    cell.setCellStyle(style);
                }
            }

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row dataRow = newSheet.getRow(i);
                dataRow.createCell(lastColumnIndex + 1).setCellValue(results.get(i - 1).doubleValue());
            }

            // 写入新的 Excel 文件
            FileOutputStream fileOutputStream = new FileOutputStream(pretreatmentFile);
            newWorkbook.write(fileOutputStream);
            fileOutputStream.close();

            // 将 JSON 数据转换为字符串
            ObjectMapper objectMapper = new ObjectMapper();
            String jsonString = objectMapper.writeValueAsString(jsonData);

            // 存储到 Redis
            stringRedisTemplate.opsForValue().set("pretreatmentData", jsonString);
            System.out.println("处理完成，新文件已保存到: " + pretreatmentFile);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void readAndStoreExcelDataToRedis(String filePath) {
        try {
            // 读取 Excel 文件
            // 判断文件是否存在
            java.io.File file = new java.io.File(filePath);
            if (!file.exists()) {
                throw new Exception("文件不存在: " + filePath);
            }
            FileInputStream fileInputStream = new FileInputStream(filePath);

            Workbook workbook = new XSSFWorkbook(fileInputStream);
            Sheet sheet = workbook.getSheetAt(0);

            // 创建 DataFormatter 实例
            DataFormatter dataFormatter = new DataFormatter();

            // 存储 JSON 数据
            List<Map<String, Object>> jsonData = new ArrayList<>();

            // 遍历每一行，进行数据提取
            for (Row row : sheet) {
                if (row.getRowNum() == 0) {
                    // 处理标题行
                    Map<String, Object> headerMap = new HashMap<>();
                    for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                        Cell cell = row.getCell(colIndex);
                        headerMap.put(dataFormatter.formatCellValue(cell), colIndex);
                    }
                    jsonData.add(headerMap);
                } else {
                    // 处理数据行
                    Map<String, Object> rowData = new HashMap<>();
                    for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                        Cell cell = row.getCell(colIndex);
                        rowData.put(dataFormatter.formatCellValue(sheet.getRow(0).getCell(colIndex)), dataFormatter.formatCellValue(cell));
                    }
                    jsonData.add(rowData);
                }
            }

            // 将 JSON 数据转换为字符串
            ObjectMapper objectMapper = new ObjectMapper();
            String jsonString = objectMapper.writeValueAsString(jsonData);

            // 存储到 Redis
            stringRedisTemplate.opsForValue().set("pretreatmentData", jsonString);

            // 关闭流
            fileInputStream.close();
            workbook.close();

            System.out.println("数据已成功读取并存储到 Redis 中。");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
